package chapter14;

import com.fasterxml.jackson.annotation.JsonProperty;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @author lzy
 * @version v1.0
 * Create DateTime: 2019/2/21 16:22
 */
public class ExcelTest {
    public static void main(String[] args) {
        List<Student> students = Arrays.asList(new Student[]{
                new Student("张三", 18, 89.87),
                new Student("李四", 17, 90.1)
        });

        String filePath = "D:\\students.xls";
//        testWrite(students, filePath);

        readAsExcel(filePath);

    }

    private static List<Student> readAsExcel(final String fileName) {
        List<Student> students = new ArrayList<>();

        try (Workbook workbook = WorkbookFactory.create(new File(fileName))) {
            for (Sheet sheet : workbook) {
                for (Row row : sheet) {
                    String name = row.getCell(0).getStringCellValue();
                    int age = (int) row.getCell(1).getNumericCellValue();
                    double score = row.getCell(2).getNumericCellValue();
                    students.add(new Student(name, age, score));
                }
            }
            System.out.println(students);
        } catch (IOException e) {
            e.printStackTrace();
        }

        return students;
    }

    private static void testWrite(List<Student> students, final String fileName) {
        try (Workbook workbook = new HSSFWorkbook();
             OutputStream out = new FileOutputStream(fileName, true)) {
           /* File file = new File(fileName);
            if (!file.exists()){
                File parent = file.getParentFile();

                if (parent == null){
                    file.mkdirs();
                }

                file.createNewFile();
            }*/

            Sheet sheet = workbook.createSheet();
            for (int i = 0; i < students.size(); i++) {
                Student student = students.get(i);
                Row row = sheet.createRow(i);
                row.createCell(0).setCellValue(student.getName());
                row.createCell(1).setCellValue(student.getAge());
                row.createCell(2).setCellValue(student.getScore());
            }
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

class Student implements Serializable {
    private static final long serialVersionUID = 2517979107353579648L;

    @JsonProperty("姓名")
    private String name;
    @JsonProperty("年龄")
    private int age;
    @JsonProperty("分数")
    private double score;

    public Student() {
    }

    public Student(String name, int age, double score) {
        this.name = name;
        this.age = age;
        this.score = score;
    }

    public String getName() {
        return name;
    }

    public int getAge() {
        return age;
    }

    public double getScore() {
        return score;
    }

    @Override
    public String toString() {
        return "Student{" +
                "name='" + name + '\'' +
                ", age=" + age +
                ", score=" + score +
                '}';
    }
}